<?php
/**
 * polwebgis
 * 
 * This file is part of polwebgis.
 *
 * @copyright   Copyright (c) 2012 Dominik Janisch (http://www.joanneum.at)
 * @lastmodified    $LastChangedDate: 2012-03-07 11:21:59 +0000 (Wed, 07 Mar 2012) $
 * @revision    $Revision: 161 $ 
 */

class RegionMapGateway extends TableDataGateway
{           

    private $where_condition_ = '';
        
    public function __construct()
    {
        $this->establishConnection();
    }
        
    public function __destruct(){}
    
    public function getDataDetails($e_id)
    {
        $stmt = $this->database_connection_->newStatement("select e_id, srid as e_projection, e_bbox, e_resolution from ebene as e, geometry_columns as gc where e_id=:e_id and f_table_name='shape_' || e.e_id");
        $stmt->bindValue(':e_id', $e_id, PDO::PARAM_INT);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_OBJ))
            return $row;
        return false;
    }
    
    public function getAvailabeMaps()
    {
        $stmt = $this->database_connection_->newStatement("select e_id, e_name from ebene order by e_id");
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[$row['e_id']] = $row['e_name'];
        return $arr_return;
    }
    
    public function getRegionByCoords($e_id, $x, $y, $base_srid)
    {
        $stmt = $this->database_connection_->newStatement("select region from shape_".$e_id." where ST_Contains(the_geom,ST_SetSRID(ST_Point(".pg_escape_string((float)$x).",".pg_escape_string((float)$y)."), :base_srid)) limit 1");
        $stmt->bindValue(':base_srid', $base_srid, PDO::PARAM_INT);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            return $row['region'];
        }
        return false;
    }
    
    public function getGeometryColumns($shape_srid, $x, $y)
    {
        $stmt = $this->database_connection_->newStatement("SELECT f_table_name, f_geometry_column, srid from geometry_columns where srid<>-1 and f_table_name like 'shape%' ");
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            if($row['srid'] != $shape_srid)
                $arr_tmp = $this->getContainingShapesReprojection($row['f_table_name'], $row['f_geometry_column'], $x, $y, $row['srid'], $shape_srid);
            else
                $arr_tmp = $this->getContainingShapes($row['f_table_name'], $row['f_geometry_column'], $x, $y, $row['srid']);
            if($arr_tmp !== false)
                $arr_return[str_replace("shape_", "", $row['f_table_name'])] = $arr_tmp;
        }
        return $arr_return;
    }
    
    public function getContainingShapes($table_name, $geom_column, $x, $y, $base_srid)
    {
        $stmt = $this->database_connection_->newStatement("select gid from ".$table_name." where ST_Contains(".$geom_column.",ST_SetSRID(ST_Point(".pg_escape_string((float)$x).",".pg_escape_string((float)$y)."), :base_srid)) limit 1");
        $stmt->bindValue(':base_srid', $base_srid, PDO::PARAM_INT);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $arr_return['e_id'] = str_replace("shape_", "", $table_name);
            $arr_return['gid'] = $row['gid']; 
            return $arr_return;
        }
        return false;
    }
    
    public function getContainingShapesReprojection($table_name, $geom_column, $x, $y, $point_srid, $shape_srid)
    {
        $stmt = $this->database_connection_->newStatement("select gid from ".$table_name." where ST_Contains(".$geom_column.",ST_Transform(ST_SetSRID(ST_Point(".pg_escape_string((float)$x).",".pg_escape_string((float)$y)."), :shape_srid), :base_srid)) limit 1");
        $stmt->bindValue(':base_srid', $point_srid, PDO::PARAM_INT);
        $stmt->bindValue(':shape_srid', $shape_srid, PDO::PARAM_INT);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $arr_return['e_id'] = str_replace("shape_", "", $table_name);
            $arr_return['gid'] = $row['gid']; 
            return $arr_return;
        }
        return false;
    }
    
    public function getAvailableData($arr_shapes, $arr_data)
    {
        $arr_final = array();
        foreach($arr_data as $key => $arr_values)
        {
            $q = "SELECT gid FROM ".$arr_values['dt_tabelle']." where gid=:gid limit 1";
            $stmt = $this->database_connection_->newStatement($q);
            $stmt->bindValue(':gid', $arr_shapes[$arr_values['e_id']]['gid'], PDO::PARAM_INT);
            $stmt->execute();
            while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
                $arr_tmp = array();
                $arr_tmp['dt_id'] = $arr_values['dt_id'];
                $arr_tmp['dt_titel'] = $arr_values['dt_titel'];
                $arr_tmp['e_name'] = $arr_values['e_name'];
                $arr_tmp['e_id'] = $arr_values['e_id'];
                $arr_tmp['dt_tabelle'] = $arr_values['dt_tabelle'];
                $arr_final[] = $arr_tmp;
            }
        }
        return $arr_final;
    }
    
    public function getAvailableTables($arr_shapes, $arr_topics)
    {
        $q = "SELECT dt_id, dt_titel, e_name, dt.e_id, dt_tabelle FROM daten_tabellen as dt, thema as t, ebene as e, tags as ta where dt.t_id=t.t_id and e.e_id=dt.e_id and dt.tag_id=ta.tag_id";
        if($arr_topics['query_only_verified_topcis'] == 1)
            $q .= " and dt_verified_data=true";
        $q .= " and e.e_id IN (";
        if($arr_topics['tg_id'] >= 0 && $arr_topics['t_id'] >= 0)
            $stmt = $this->getSpecificTables($q, $arr_topics, $arr_shapes);
        else
            $stmt = $this->getAllAvailableTables($q, $arr_shapes);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[] = $row;
        return $arr_return;
    }
    
    private function getAllAvailableTables($q, $arr_shapes)
    {
        $q = $this->addShapesToQuery($q, $arr_shapes);
        $q .= ") order by dt_titel";
        $stmt = $this->database_connection_->newStatement($q);
        $this->bindShapesToQuery($stmt, $arr_shapes);
        return $stmt;
    }
    
    private function getSpecificTables($q, $arr_topics, $arr_shapes)
    {
        $q = $this->addShapesToQuery($q, $arr_shapes);
        $q .= ")";
        if($arr_topics['tg_id'] > 0  && $arr_topics['t_id'] > 0)
            $q .= " and (ta.tag_parent_id=? and t.t_id=?)";
        elseif($arr_topics['tg_id'] > 0 && $arr_topics['t_id'] == 0)
            $q .= " and ta.tag_parent_id=?";
        elseif($arr_topics['tg_id'] == 0 && $arr_topics['t_id'] > 0)
            $q .= " and t.t_id=?";
        $q .= " order by dt_titel";
        $stmt = $this->database_connection_->newStatement($q);
        $this->bindShapesToQuery($stmt, $arr_shapes);
        $param_count = count($arr_shapes);
        if($arr_topics['tg_id'] > 0)
            $stmt->bindValue(++$param_count, $arr_topics['tg_id'], PDO::PARAM_INT);
        if($arr_topics['t_id'] > 0)
            $stmt->bindValue(++$param_count, $arr_topics['t_id'], PDO::PARAM_INT);
        return $stmt;
    }
    
    private function addShapesToQuery($q, $arr_shapes)
    {
        for($cnt = 0; $cnt < count($arr_shapes); ++$cnt)
        {
            if($cnt > 0)
                $q .= ", ";
            $q .= "?";
        }
        return $q;   
    }
    
    private function bindShapesToQuery($stmt, $arr_shapes)
    {
        $cnt = 0;
        foreach($arr_shapes as $arr_values)
            $stmt->bindValue(++$cnt, $arr_values['e_id'], PDO::PARAM_INT);
        return $stmt; 
    }
}

?>
